package edu.hunau.cxb17.dao.impl;

import edu.hunau.cxb17.dao.IUserDao;
import edu.hunau.cxb17.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Repository
public class UserDaoImpl implements IUserDao {

    //注入JdbcTemplate的对象
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void update(User u) {

    }

    @Override
    public void saveBatch(List<User> users) {
        String sql = "INSERT INTO t_user(username,pwd) VALUES(?,?) ";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User u = users.get(i);
                ps.setString(1, u.getUsername());
                ps.setString(2, u.getPwd());
            }

            @Override
            public int getBatchSize() {
                //批处理的语句个数
                return users.size();
            }
        });
    }


    @Override
    public void delete(User u) {

    }

    @Override
    public void save(User u) {
        String sql = "insert into t_user(username,pwd) values(?,md5(?))";
        //准备参数:参数提供的顺序必须和sql语句中的问号对应
        Object[] args = {u.getUsername(), u.getPwd()};
        jdbcTemplate.update(sql, args);
    }

    @Override
    public List<User> queryByPage(int begin, int size) {
        String sql = "select id,username,pwd,email,NAME,gender,birthday,ustate,checkcode,dotime,earned from t_user limit ?,?";
        Object[] args = {begin, size};
        return jdbcTemplate.query(sql, args, new UserRowMapper());
    }

    @Override
    public Integer saveReturnPk(User u) {
        String sql = "insert into t_user(username, pwd) values(?,md5(?))";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        //明确主键信息
                        PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
                        ps.setString(1, u.getUsername());
                        ps.setString(2, u.getPwd());
                        return ps;
                    }
                },
                keyHolder);
        //从keyHolder对象中获得返回值的信息
        return keyHolder.getKey().intValue();
    }

    @Override
    public List<User> queryAllUsers() {
        return null;
    }

    @Override
    public User queryById(Integer id) {
        String sql = "select id,username,pwd,gender,dotime from t_user where id =?";
        return jdbcTemplate.queryForObject(sql, (rs, i) -> {
            //直接操作结果封装对象
            User u = new User();
            u.setId(rs.getInt("id"));
            u.setPwd(rs.getString("pwd"));
            u.setDotime(rs.getTimestamp("dotime"));
            u.setGender(rs.getString("gender"));
            return u;
        }, id);
    }

    @Override
    public List<User> queryByLikeName(String datas) {
        String sql = "select id,username,pwd,email,NAME,gender,birthday,ustate,checkcode,dotime,earned from t_user WHERE username like concat('%',?,'%')";
        Object[] args = {datas};
        return jdbcTemplate.query(sql, args, new UserRowMapper());
    }

    class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int i) throws SQLException {
            User user = new User();
            user.setUsername(rs.getString("username"));
            user.setPwd(rs.getString("pwd"));
            return user;
        }
    }
}
